package pe.sonrisas.tools.mysql;


import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import pe.sonrisas.tools.entities.Persona;

public class PersonaDAOMysql {


    Conexion_BD co=new Conexion_BD();
    ResultSet rs = null;
    CallableStatement cs;


    public void agregarPersona(Persona nuevaPersona) throws SQLException{
       
        try {
            co.Conectar();
            co.getCn().setAutoCommit(false);
            cs = co.getCn().prepareCall("{CALL usp_registrar_persona(?,?,?,?,?,?,?,?,?,?,?,?)}");
            
            
            cs.setString(1, nuevaPersona.getNombres());
            cs.setString(2, nuevaPersona.getApellidoPaterno());
            cs.setString(3, nuevaPersona.getApellidoMaterno());
            cs.setString(4, nuevaPersona.getDni());
            cs.setString(5, nuevaPersona.getNacionalidad());
            cs.setString(6, nuevaPersona.getDomicilio());
            cs.setString(7, nuevaPersona.getEmail());
            cs.setString(8, nuevaPersona.getTelefono());
            cs.setString(9, nuevaPersona.getCelular());
            cs.setString(10, nuevaPersona.getFechanac().toString());
            cs.setString(11, ""+nuevaPersona.getGenero());
            cs.setInt(12,   nuevaPersona.getIdUbigeo().getIdUbigeo().intValue());      
            
            cs.executeUpdate();
            co.getCn().commit();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(PersonaDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PersonaDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
            co.getCn().rollback();
        }
        
        finally {
            try {
                co.getCn().setAutoCommit(true);
                co.Desconectar();               
            } catch (Exception ex) {
                Logger.getLogger(PersonaDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
            }
                    }
        }
    
    
    
    public void agregarPersona(Persona nuevaPersona,int ubigeo){
        try {
            co.Conectar();
            System.out.println("Registrando");
            //cs = co.getCn().prepareCall("{CALL usp_registrar_persona(?,?,?,?,?,?,?,?,?,?,?,?)}");
            
            
            String query = "INSERT INTO persona (idUbigeo, nombres, apellidoPaterno, apellidoMaterno,telefono, celular) VALUES ("+ubigeo+",'"+nuevaPersona.getNombres()+"','"+nuevaPersona.getApellidoPaterno()+"','"+nuevaPersona.getApellidoMaterno()+"','"+nuevaPersona.getTelefono()+"','"+nuevaPersona.getCelular()+"')"; 
            System.out.println(query);
            co.insupdel(query);
            
            /*
            cs.setString(1, nuevaPersona.getNombres());
            cs.setString(2, nuevaPersona.getApellidoPaterno());
            cs.setString(3, nuevaPersona.getApellidoMaterno());
            cs.setNull(4, java.sql.Types.NULL);
            cs.setNull(5, java.sql.Types.NULL);
            cs.setNull(6, java.sql.Types.NULL);
            cs.setNull(7, java.sql.Types.NULL);
            cs.setString(8, nuevaPersona.getTelefono());
            cs.setString(9, nuevaPersona.getCelular());
            cs.setNull(10, java.sql.Types.NULL);
            cs.setNull(11, java.sql.Types.NULL);
            cs.setInt(12,   ubigeo); 
            */
            
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }        
    }
    
    
           
    
    

    public int cargarCodigo(){
        int codigo = 0;
        try {
            co.Conectar();
            rs = co.Consultar("select MAX(idPERSONA) from PERSONA");
            rs.first();
            codigo = Integer.parseInt(rs.getString(1));
            try {
                co.Desconectar();
            } catch (Exception ex) {
                Logger.getLogger(PacienteDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
            }
           return codigo;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(PacienteDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PacienteDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
        }
        return codigo;

    }


    public boolean editarPersona(int codPaciente,Persona persona){
        boolean modifico = false;
          try {
            co.Conectar();
            co.getCn().setAutoCommit(false);
            //Llamamos el Store Procedure 
            cs = co.getCn().prepareCall("{CALL usp_modificar_persona(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            //Enviamos parametros al Store Procedure
            cs.setInt   (1, codPaciente);
            cs.setString(2, persona.getNombres());            
            cs.setString(3, persona.getApellidoPaterno());
            cs.setString(4, persona.getApellidoMaterno());
            cs.setString(5, persona.getDni());
            cs.setString(6, persona.getNacionalidad());
            cs.setString(7, persona.getDomicilio());
            cs.setString(8, persona.getEmail());
            cs.setString(9, persona.getTelefono());
            cs.setString(10, persona.getCelular());
            cs.setString(11, persona.getFechanac().toString());
            cs.setString(12, ""+persona.getGenero());
            cs.setInt(13, persona.getIdUbigeo().getIdUbigeo().intValue());

            //Ejecutamos el Store Procedure
            cs.executeUpdate();
            //Comiteamos
            co.getCn().commit();
            modifico=true;
            return modifico;
            //String query = "execute sp_registrar_paciente " + "'" + paciente.getCodpaciente() + "'" + "," + "'" + paciente.getCodpersona() + "'" + "," + "'" + paciente.getAntecedentes() + "'" + "," + "'" + paciente.getOcupacion() + "'" + "," + "'" + paciente.getRecomendado() + "'" + "," + "'" + paciente.getGrupo() + "'" + "," + "'" + paciente.getFechaingreso() + "'";
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(PacienteDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PacienteDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
            try {
                co.getCn().rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(PacienteDAOMysql.class.getName()).log(Level.SEVERE, null, ex1);
            }
        } finally {
            try {
                co.getCn().setAutoCommit(true);
                co.Desconectar();
            } catch (Exception ex) {
                Logger.getLogger(PersonaDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        /*
            try {

            co.Conectar();
            String query = "execute sp_modificar_persona  '" + codPaciente +"'" + "," + "'" + persona.getNombres() + "'" + "," + "'" + persona.getApellidos() + "'" + "," + "'" + persona.getDni() + "'" + "," + "'" + persona.getNacionalidad() + "'" + "," + "'" + persona.getDomicilio() + "'" + "," +  "'" + persona.getEmail() + "'" + "," + "'" + persona.getTelefono() + "'" + "," + "'" + persona.getCelular() + "'" + "," + "'" + persona.getFechanac() + "'" + "," + "'" + persona.getGenero() + "'" + "," + "'" + persona.getId_ubigeo() + "'";
            co.insupdel(query);
            modifico=true;
            return modifico;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }

        try {
            co.Desconectar();
        } catch (Exception ex) {
            Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        }*/

        return modifico;
        

    }

}
